package multiplex.dataaccess.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import multiplex.dataaccess.DaoException;
import multiplex.model.Filme;
import multiplex.model.Localizacao;
import multiplex.model.Sala;
import multiplex.model.SalaTipo;

public class SalaDao extends AbstractDao{
	
	public Sala get(int id) throws DaoException {
		try{
			PreparedStatement st = getConnection().prepareStatement("SELECT * FROM Sala WHERE id =?");
			st.setInt(1, id);
			ResultSet rs = st.executeQuery();
			if(rs.next()){
				Sala sala = new Sala();
				fillSala(sala, rs);
				st.close();
				return sala;
			}
		} catch(Exception e){
			throw new DaoException(e);
		}
		return null;
	}

	public List<Sala> getAll() throws DaoException {
		List<Sala> salaList = new ArrayList<Sala>();
		try{
			PreparedStatement st = getConnection().prepareStatement("SELECT * FROM Sala");
			
			ResultSet rs = st.executeQuery();
			while(rs.next()){
				Sala sala = new Sala();
				fillSala(sala, rs);
				salaList.add(sala);
			}
			st.close();
			return salaList;
		}catch(Exception e){
			throw new DaoException(e);
		}
	}
	
	public List<Sala> getAllFromLocalizacao(Localizacao localizacao) throws DaoException {
		
		List<Sala> salaList = new ArrayList<Sala>();
		try{
			PreparedStatement st = getConnection().prepareStatement(
					"SELECT * FROM Sala WHERE ?=Sala.Localizacao_id");
			st.setInt(1, localizacao.getId());
			ResultSet rs = st.executeQuery();
			while(rs.next()){
				Sala sala = new Sala();
				fillSala(sala, rs);
				salaList.add(sala);
			}
			st.close();
			return salaList;
		}catch(Exception e){
			throw new DaoException(e);
		}
	}
	
	public List<Sala> getAllFromLocalizacaoAndFilme(Localizacao localizacao, Filme filme) 
		throws DaoException {

		List<Sala> salaList = new ArrayList<Sala>();
		try{
			PreparedStatement st = getConnection().prepareStatement(
					"SELECT DISTINCT Sala.id AS id, Sala.salaTipo AS salaTipo, " +
					"Sala.Localizacao_id AS Localizacao_id, Sala.tecno3D AS tecno3D FROM " +
					"Sala, Sessao, Filme WHERE " +
					"Sala.id=Sessao.Sala_id AND Sessao.Filme_id=Filme.id AND " +
					"Sala.Localizacao_id=? AND Sessao.Filme_id=?");
			st.setInt(1, localizacao.getId());
			st.setInt(2, filme.getId());
			ResultSet rs = st.executeQuery();
			while(rs.next()){
				Sala sala = new Sala();
				fillSala(sala, rs);
				salaList.add(sala);
			}
			st.close();
			return salaList;
		}catch(Exception e){
			throw new DaoException(e);
		}
	}

	public void save(Sala sala) throws DaoException {
		try{
			PreparedStatement st = getConnection().prepareStatement(
					"INSERT INTO Sala (salaTipo, tecno3D, Localizacao_id) VALUES (?,?,?)", 
					Statement.RETURN_GENERATED_KEYS);
			fillPreparatedStatement(st, sala);
			st.execute();
			ResultSet rs = st.getGeneratedKeys();
			rs.next();
			sala.setId(rs.getInt(1));
			st.close();
		}catch(Throwable e){
			throw new DaoException(e);
		}
	}
	
	public void update(Sala sala) throws DaoException {
		try{
			PreparedStatement st = getConnection().prepareStatement(
					"UPDATE Sala SET salaTipo=?, tecno3D=?, Localizacao_id=? WHERE id=?");
			fillPreparatedStatement(st, sala);
			st.setInt(4, sala.getId());
			st.execute();
			st.close();
		}catch(Throwable e){
			throw new DaoException(e);
		}
	}

	public void remove(Sala sala) throws DaoException {
		try{
			PreparedStatement st = getConnection().prepareStatement(
					"DELETE FROM Sala WHERE id=?");
			st.setInt(1, sala.getId());
			st.execute();
			st.close();
		} catch(Throwable e){
			throw new DaoException(e);
		}
	}
	
	private void fillSala(Sala sala, ResultSet rs) throws SQLException {
		sala.setId(rs.getInt("id"));
		// recuperar salaTipo
		int salaTipoId = rs.getInt("salaTipo");
		SalaTipo[] tipos = SalaTipo.values();
		for (SalaTipo tipo : tipos) {
			if (tipo.getId()==salaTipoId) {
				sala.setSalaTipo(tipo);
				break;
			}
		}
		sala.setTecno3D(rs.getBoolean("tecno3D"));
		// recuperar localizacao
		int localizacaoId = rs.getInt("Localizacao_id");
		LocalizacaoDao localizacaoDao = new LocalizacaoDao();
		sala.setLocalizacao(localizacaoDao.get(localizacaoId));
	}
	
	private void fillPreparatedStatement(PreparedStatement st, Sala sala) throws SQLException {
		st.setInt(1, sala.getSalaTipo().getId());
		st.setBoolean(2, sala.isTecno3D());
		st.setInt(3, sala.getLocalizacao().getId());
	}
	
}
